package com.dlm.service.impl;

import com.dlm.dao.Item;
import com.dlm.service.IItemService;
import com.jfinal.plugin.activerecord.Page;

import java.util.List;

/**
 * Created by chongsong on 15/2/1.
 * <mailto>chong.song@autonavi.com</mailto>
 */
public class ItemServiceImpl implements IItemService{
    @Override
    public boolean add(Item item) throws Exception {
        return item.save();
    }

    @Override
    public boolean delete(long id) throws Exception {
        return Item.dao.deleteById(id);
    }

    @Override
    public boolean update(Item item) throws Exception {
        return item.update();
    }

    @Override
    public Item findById(long id) throws Exception {
        String sql = "select a.*,e.pd,e.cd,e.td from (select i.id as item_id,i.tech_id,i.flow,i.function,i.tips,i.spend_time,i.take_time," +
                "i.price,i.old_price,i.main_img,i.name as item_name,i.desc,t.name as tech_name,t.comment as tech_comment " +
                " from item i LEFT JOIN technician t on i.tech_id = t.id where i.id = " + id
                + ") a left join evaluate e on a.tech_id = e.tech_id";
        List<Item> items = Item.dao.find(sql);
        if(items != null && items.size() > 0) {
            return items.get(0);
        }
        return null;
    }

    @Override
    public Page<Item> findByPage(long techId,int pn,int ps, double x, double y) throws Exception {
        if(techId <= 0){
//            String center = "GeomFromText('POINT(" + x + " " + y + ")')";
//            int radius = 10;
//            Object[] param = {};
//            String select = "SELECT i.id as itemId,i.desc,i.spend_time,i.name as itemName,i.price,i.old_price,i.main_img,i.flow,i.function,i.tips,t.name as techName,SQRT(POW(ABS(X(t.pos)-X(" + center + ")),2)+POW(ABS(Y(t.pos)-Y(" + center + ")),2)) AS distance";
//            String from = "FROM item i,technician t WHERE i.tech_id = t.id ";
//            from += " and SQRT(POW(ABS(X(t.pos)-X(" + center + ")),2)+POW(ABS(Y(t.pos)-Y(" + center + ")),2))<" + radius + " order by i.id";
//            System.out.println(select);
//            System.out.println(from);
//            return Item.dao.paginate(pn, ps, select, from, param);
            Object[] param = {};
            String select = "SELECT i.id as itemId,i.desc,i.spend_time,i.name as itemName,i.price,i.old_price,i.main_img,i.flow,i.function,i.tips,t.name as techName";
            String from = "FROM item i LEFT JOIN technician t ON t.id = i.tech_id ";
            return Item.dao.paginate(pn, ps, select, from, param);

        }else{
            String select = "SELECT i.id AS itemId, i.DESC, i.spend_time, i.NAME AS itemName, i.price, i.old_price, i.main_img, " +
                    "i.flow, i.FUNCTION, i.tips, tem.t_price,tem.t_name, tem.use_pro,tem.spend_time,tem.old_price,tem.ti_id ";
            String from = " FROM ( SELECT ti.id ti_id,ti.tech_id, ti.price t_price,ti.name t_name," +
                    "ti.use_pro use_pro, ri.item_id,ti.spend_time,ti.old_price FROM t_item ti, r_item ri, item i WHERE ti.tech_id = " + techId + " and " +
                    "i.id = ri.item_id AND ri.ti_id = ti.id ) tem LEFT JOIN item i ON i.id = tem.item_id ORDER BY i.id";
            Object[] param = {};
            System.out.println(select + from);
            return Item.dao.paginate(pn, ps, select,from,param);
        }
    }
}
